﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using ADO.Net;

class ADOMain
{
    public static void FindProducts()
    {
        SqlConnection dbCon = new SqlConnection(Settings.Default.connectionString);
        dbCon.Open();
        using (dbCon)
        {
            SqlCommand command = new SqlCommand("SELECT ProductName, CategoryName FROM Products p JOIN Categories c " +
                                                "ON p.CategoryID = c.CategoryID", dbCon);
            SqlDataReader reader = command.ExecuteReader();
            Dictionary<string, List<string>> products = new Dictionary<string, List<string>>();
            using (reader)
            {
                while (reader.Read())
                {
                    if (products.ContainsKey(reader.GetString(1)))
                    {
                        products[reader.GetString(1)].Add(reader.GetString(0));
                    }
                    else
                    {
                        products.Add(reader.GetString(1), new List<string>() { reader.GetString(0) });
                    }
                }
            }
            foreach (var pair in products)
            {
                Console.WriteLine("The products in category {0} are:", pair.Key);
                Console.WriteLine("----------------------------------------");
                foreach (var product in products[pair.Key])
                {
                    Console.WriteLine(product);
                }
                Console.WriteLine();
            }

        }
    }

    public static void AddAProduct()
    {
        string pName = "someProduct";
        int? sID = null;
        int? cID = 7;
        string qpu = null;

        SqlConnection dbCon = new SqlConnection(Settings.Default.connectionString);
        dbCon.Open();
        using (dbCon)
        {
            SqlCommand command = new SqlCommand("INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit)" +
                                                "VALUES (@pName, @sID, @cID, @qpu)", dbCon);
            command.Parameters.Add(new SqlParameter("@pName", pName));
            SqlParameter sIDPar = new SqlParameter("@sID", sID);
            command.Parameters.Add(sIDPar);
            SqlParameter cIDPar = new SqlParameter("@cID", cID);
            command.Parameters.Add(cIDPar);
            SqlParameter qpuPar = new SqlParameter("@qpu", qpu);
            command.Parameters.Add(qpuPar);
            if (sID == null)
            {
                sIDPar.Value = DBNull.Value;
            }
            if (cID == null)
            {
                cIDPar.Value = DBNull.Value;
            }
            if (qpu == null)
            {
                qpuPar.Value = DBNull.Value;
            }
            Console.WriteLine(command.ExecuteNonQuery());
        }
    }

    static void Main()
    {
        SqlConnection dbCon = new SqlConnection(Settings.Default.masterConString);
        dbCon.Open();
        using (dbCon)
        {
            SqlCommand command = new SqlCommand("CREATE DATABASE ATM", dbCon);
            command.ExecuteNonQuery();
        }
        dbCon = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=ATM;Integrated Security=True");
        dbCon.Open();
        using(dbCon)
        {
            SqlCommand command = new SqlCommand("CREATE TABLE CardAccounts " +
                                            "(" +
                                                "Id int IDENTITY," +
                                                "CardNumber char(10) NOT NULL," +
                                                "CardPin char(4) NOT NULL," +
                                                "CardCash money NOT NULL," +
                                                "CONSTRAINT PK_Atm PRIMARY KEY(Id)" +
                                            ")", dbCon);
            command.ExecuteNonQuery();
        }
    }
}
